﻿using System;

namespace Citi.PTSConsolidation.Domain
{
    public class GenericBookOfWork
    {
        public static string GetQueryHistoryBOW(string tableName, int year, int daysToArchive)
        {
            //INSERT INTO PtsHistory.dbo.{2}History{1}
            //        SELECT * FROM  {2}{1} 
            //        WHERE ReferenceDate < GETDATE() - {0}
            return string.Format(@"DELETE FROM {2}{1}
                    WHERE ReferenceDate < GETDATE() - {0}
                    ", daysToArchive, year, tableName);
        }

        public static string GetQueryDuplicateData(string tableName, int year, DateTime referenceDate)
        {
            return string.Format(@"DELETE FROM {2}{0} WHERE ReferenceDate = '{1}'", year, referenceDate.ToString("yyyy-MM-dd"), tableName);
        }

        public static string GetQueryDeleteNonBr(string tableName, int year)
        {
            return string.Format(@"DELETE {1}{0} FROM {1}{0}
                    INNER JOIN GOC AS ClientGOC ON ClientGOC.GocAttributes = ClientGocCode
                    INNER JOIN GOC AS SystemGOC ON SystemGOC.GocAttributes = SystemGocCode
                    Where ClientGOC.ManagedGeography <> 'BR' AND SystemGOC.ManagedGeography <> 'BR'", year, tableName);
        }

        public static string GetQueryValidateFullYear(string tableName, int year, DateTime currentReferenceDate)
        {
            return string.Format(@"DECLARE @PreviousFullYear int, @CurrentFullYear int, @PreviousDate date

                    SELECT @PreviousDate = Max(ReferenceDate) FROM {0}{1} WHERE ReferenceDate < '{2}'
                    SELECT @PreviousFullYear = ISNULL(Sum(FullYear), 0) FROM {0}{1} WHERE ReferenceDate = @PreviousDate
                    SELECT @CurrentFullYear = ISNULL(Sum(FullYear), 0) FROM {0}{1} WHERE ReferenceDate = '{2}'

                    DECLARE @VarianceAcceptable int 
                    SET @VarianceAcceptable = @PreviousFullYear / 10

                    IF @PreviousFullYear - @CurrentFullYear > @VarianceAcceptable OR @CurrentFullYear - @PreviousFullYear > @VarianceAcceptable
	                    SELECT 0
                    ELSE
                        SELECT 1", 
                    tableName, year, currentReferenceDate.ToString("yyyy-MM-dd"));
        }
    }
}
